{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-11T06:52:52.737362Z",
     "start_time": "2019-11-11T06:52:52.668577Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>记录数</th>\n",
       "      <th>合作方</th>\n",
       "      <th>地市</th>\n",
       "      <th>完成时间</th>\n",
       "      <th>总竣工人数</th>\n",
       "      <th>用户类型</th>\n",
       "      <th>省份</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>6120</td>\n",
       "      <td>2</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201910</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>安康市</td>\n",
       "      <td>201910</td>\n",
       "      <td>60</td>\n",
       "      <td>10</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>130</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>漳州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>福建省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>咸阳市</td>\n",
       "      <td>201910</td>\n",
       "      <td>1375</td>\n",
       "      <td>6</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>1260</td>\n",
       "      <td>1</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1</td>\n",
       "      <td>YUANDONGRONGZU</td>\n",
       "      <td>海口市</td>\n",
       "      <td>201910</td>\n",
       "      <td>4230</td>\n",
       "      <td>2</td>\n",
       "      <td>海南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>漳州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>62</td>\n",
       "      <td>9</td>\n",
       "      <td>福建省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1</td>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>17</td>\n",
       "      <td>9</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>YUANDONGRONGZU</td>\n",
       "      <td>太原市</td>\n",
       "      <td>201910</td>\n",
       "      <td>344</td>\n",
       "      <td>2</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>绵阳市</td>\n",
       "      <td>201910</td>\n",
       "      <td>2535</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>141</td>\n",
       "      <td>7</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1</td>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201910</td>\n",
       "      <td>339</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>广州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>2583</td>\n",
       "      <td>2</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201910</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1</td>\n",
       "      <td>WEIXIN</td>\n",
       "      <td>海口市</td>\n",
       "      <td>201910</td>\n",
       "      <td>2181</td>\n",
       "      <td>2</td>\n",
       "      <td>海南省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>宝鸡市</td>\n",
       "      <td>201910</td>\n",
       "      <td>23</td>\n",
       "      <td>10</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201910</td>\n",
       "      <td>897</td>\n",
       "      <td>1</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>太原市</td>\n",
       "      <td>201910</td>\n",
       "      <td>945</td>\n",
       "      <td>6</td>\n",
       "      <td>山西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1</td>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>南充市</td>\n",
       "      <td>201910</td>\n",
       "      <td>447</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>沈阳市</td>\n",
       "      <td>201909</td>\n",
       "      <td>39</td>\n",
       "      <td>7</td>\n",
       "      <td>辽宁省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201910</td>\n",
       "      <td>609</td>\n",
       "      <td>1</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1</td>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>ZHAOLIAN</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>201909</td>\n",
       "      <td>2094</td>\n",
       "      <td>2</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>汉中市</td>\n",
       "      <td>201910</td>\n",
       "      <td>2098</td>\n",
       "      <td>6</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>268</td>\n",
       "      <td>1</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1</td>\n",
       "      <td>ZHAOLIAN</td>\n",
       "      <td>张掖市</td>\n",
       "      <td>201910</td>\n",
       "      <td>200</td>\n",
       "      <td>2</td>\n",
       "      <td>甘肃省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>603</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>重庆市辖区</td>\n",
       "      <td>201904</td>\n",
       "      <td>23</td>\n",
       "      <td>7</td>\n",
       "      <td>重庆市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <td>1</td>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201906</td>\n",
       "      <td>230</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>605</th>\n",
       "      <td>1</td>\n",
       "      <td>ZHONGAN</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>201907</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>606</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>西安市</td>\n",
       "      <td>201910</td>\n",
       "      <td>667</td>\n",
       "      <td>10</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>重庆市辖区</td>\n",
       "      <td>201907</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "      <td>重庆市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>1</td>\n",
       "      <td>ZHAOLIAN</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201904</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>609</th>\n",
       "      <td>1</td>\n",
       "      <td>ZIZHUSHOUXIN</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201905</td>\n",
       "      <td>1268</td>\n",
       "      <td>1</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>重庆市辖区</td>\n",
       "      <td>201904</td>\n",
       "      <td>322</td>\n",
       "      <td>6</td>\n",
       "      <td>重庆市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>1</td>\n",
       "      <td>WEIXIN</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201906</td>\n",
       "      <td>2354</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201904</td>\n",
       "      <td>74</td>\n",
       "      <td>6</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>1</td>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>惠州市</td>\n",
       "      <td>201905</td>\n",
       "      <td>32</td>\n",
       "      <td>9</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>1</td>\n",
       "      <td>ZIZHUSHOUXIN</td>\n",
       "      <td>惠州市</td>\n",
       "      <td>201904</td>\n",
       "      <td>123</td>\n",
       "      <td>1</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>615</th>\n",
       "      <td>1</td>\n",
       "      <td>DUMIAO</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>201908</td>\n",
       "      <td>3586</td>\n",
       "      <td>2</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>616</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201909</td>\n",
       "      <td>341</td>\n",
       "      <td>1</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>617</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201908</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>618</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>淮安市</td>\n",
       "      <td>201909</td>\n",
       "      <td>814</td>\n",
       "      <td>1</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>619</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201907</td>\n",
       "      <td>20</td>\n",
       "      <td>7</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>620</th>\n",
       "      <td>1</td>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201908</td>\n",
       "      <td>21</td>\n",
       "      <td>9</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>621</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>201909</td>\n",
       "      <td>354</td>\n",
       "      <td>10</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201907</td>\n",
       "      <td>278</td>\n",
       "      <td>6</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>1</td>\n",
       "      <td>HUABEI</td>\n",
       "      <td>荆门市</td>\n",
       "      <td>201907</td>\n",
       "      <td>124</td>\n",
       "      <td>6</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>624</th>\n",
       "      <td>1</td>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>625</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201907</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>626</th>\n",
       "      <td>1</td>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>201908</td>\n",
       "      <td>105</td>\n",
       "      <td>9</td>\n",
       "      <td>浙江省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>627</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201908</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>四川省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>628</th>\n",
       "      <td>1</td>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201907</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>1</td>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>206</td>\n",
       "      <td>1</td>\n",
       "      <td>湖北省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>1</td>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201909</td>\n",
       "      <td>26</td>\n",
       "      <td>9</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>631</th>\n",
       "      <td>1</td>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>渭南市</td>\n",
       "      <td>201910</td>\n",
       "      <td>968</td>\n",
       "      <td>2</td>\n",
       "      <td>陕西省</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>632</th>\n",
       "      <td>1</td>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>佛山市</td>\n",
       "      <td>201910</td>\n",
       "      <td>39</td>\n",
       "      <td>10</td>\n",
       "      <td>广东省</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>633 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     记录数              合作方     地市    完成时间  总竣工人数  用户类型        省份\n",
       "0      1     FENQICHAOREN    榆林市  201910   6120     2       陕西省\n",
       "1      1         JINGDONG  乌鲁木齐市  201910     16     7  新疆维吾尔自治区\n",
       "2      1           YUEBAO    安康市  201910     60    10       陕西省\n",
       "3      1     FENQICHAOREN    南京市  201910    130     2       江苏省\n",
       "4      1         JINGDONG    漳州市  201910     13     7       福建省\n",
       "5      1           HUABEI    咸阳市  201910   1375     6       陕西省\n",
       "6      1  TIANCHENGRONGZU    榆林市  201910   1260     1       陕西省\n",
       "7      1   YUANDONGRONGZU    海口市  201910   4230     2       海南省\n",
       "8      1         DELEKEJI    漳州市  201910     62     9       福建省\n",
       "9      1         DELEKEJI    南京市  201909     17     9       江苏省\n",
       "10     1   YUANDONGRONGZU    太原市  201910    344     2       山西省\n",
       "11     1     FENQICHAOREN    绵阳市  201910   2535     2       四川省\n",
       "12     1         JINGDONG    南京市  201910     21     7       江苏省\n",
       "13     1         JINGDONG    榆林市  201910    141     7       陕西省\n",
       "14     1   MASHANGXIAOJIN    成都市  201910    339     2       四川省\n",
       "15     1   MASHANGXIAOJIN    广州市  201910   2583     2       广东省\n",
       "16     1         JINGDONG    东莞市  201910     16     7       广东省\n",
       "17     1           WEIXIN    海口市  201910   2181     2       海南省\n",
       "18     1           YUEBAO    宝鸡市  201910     23    10       陕西省\n",
       "19     1  TIANCHENGRONGZU    东莞市  201910    897     1       广东省\n",
       "20     1           HUABEI    太原市  201910    945     6       山西省\n",
       "21     1         DELEKEJI    南京市  201910     12     9       江苏省\n",
       "22     1   MASHANGXIAOJIN    南充市  201910    447     2       四川省\n",
       "23     1         JINGDONG    沈阳市  201909     39     7       辽宁省\n",
       "24     1  TIANCHENGRONGZU  乌鲁木齐市  201910    609     1  新疆维吾尔自治区\n",
       "25     1     SHIJIYINTONG    黄冈市  201908      1     9       湖北省\n",
       "26     1         ZHAOLIAN    武汉市  201909   2094     2       湖北省\n",
       "27     1           HUABEI    汉中市  201910   2098     6       陕西省\n",
       "28     1  TIANCHENGRONGZU    南京市  201910    268     1       江苏省\n",
       "29     1         ZHAOLIAN    张掖市  201910    200     2       甘肃省\n",
       "..   ...              ...    ...     ...    ...   ...       ...\n",
       "603    1         JINGDONG  重庆市辖区  201904     23     7       重庆市\n",
       "604    1   MASHANGXIAOJIN    淮安市  201906    230     2       江苏省\n",
       "605    1          ZHONGAN    武汉市  201907      2     1       湖北省\n",
       "606    1           YUEBAO    西安市  201910    667    10       陕西省\n",
       "607    1         JINGDONG  重庆市辖区  201907     21     7       重庆市\n",
       "608    1         ZHAOLIAN    淮安市  201904      1     2       江苏省\n",
       "609    1     ZIZHUSHOUXIN    淮安市  201905   1268     1       江苏省\n",
       "610    1           HUABEI  重庆市辖区  201904    322     6       重庆市\n",
       "611    1           WEIXIN    淮安市  201906   2354     2       江苏省\n",
       "612    1           HUABEI    东莞市  201904     74     6       广东省\n",
       "613    1     SHIJIYINTONG    惠州市  201905     32     9       广东省\n",
       "614    1     ZIZHUSHOUXIN    惠州市  201904    123     1       广东省\n",
       "615    1           DUMIAO    杭州市  201908   3586     2       浙江省\n",
       "616    1  TIANCHENGRONGZU    中山市  201909    341     1       广东省\n",
       "617    1           YUEBAO    淮安市  201908      4    10       江苏省\n",
       "618    1  TIANCHENGRONGZU    淮安市  201909    814     1       江苏省\n",
       "619    1         JINGDONG    南京市  201907     20     7       江苏省\n",
       "620    1         DELEKEJI    成都市  201908     21     9       四川省\n",
       "621    1           YUEBAO    武汉市  201909    354    10       湖北省\n",
       "622    1           HUABEI    中山市  201907    278     6       广东省\n",
       "623    1           HUABEI    荆门市  201907    124     6       湖北省\n",
       "624    1     FENQICHAOREN    南京市  201909     17     2       江苏省\n",
       "625    1         JINGDONG    东莞市  201907      6     7       广东省\n",
       "626    1     SHIJIYINTONG    杭州市  201908    105     9       浙江省\n",
       "627    1  TIANCHENGRONGZU    成都市  201908      2     1       四川省\n",
       "628    1         JINGDONG  乌鲁木齐市  201907     10     7  新疆维吾尔自治区\n",
       "629    1  TIANCHENGRONGZU    黄冈市  201908    206     1       湖北省\n",
       "630    1         DELEKEJI    中山市  201909     26     9       广东省\n",
       "631    1   MASHANGXIAOJIN    渭南市  201910    968     2       陕西省\n",
       "632    1           YUEBAO    佛山市  201910     39    10       广东省\n",
       "\n",
       "[633 rows x 7 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入百融csv文件\n",
    "import pandas as pd\n",
    "import os\n",
    "os.chdir(r'D:\\vintage处理')\n",
    "df=pd.read_csv('vintage总人数.csv',low_memory=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-11T06:52:58.726960Z",
     "start_time": "2019-11-11T06:52:58.386838Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>合作方</th>\n",
       "      <th>用户类型</th>\n",
       "      <th>省份</th>\n",
       "      <th>地市</th>\n",
       "      <th>完成时间</th>\n",
       "      <th>账期</th>\n",
       "      <th>逾期人数</th>\n",
       "      <th>总竣工人数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>2</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>6120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>10</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>安康市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>福建省</td>\n",
       "      <td>漳州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>咸阳市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>1375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>1260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>YUANDONGRONGZU</td>\n",
       "      <td>2</td>\n",
       "      <td>海南省</td>\n",
       "      <td>海口市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>4230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>福建省</td>\n",
       "      <td>漳州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>201910</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>YUANDONGRONGZU</td>\n",
       "      <td>2</td>\n",
       "      <td>山西省</td>\n",
       "      <td>太原市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "      <td>绵阳市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>2535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>榆林市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>2</td>\n",
       "      <td>广东省</td>\n",
       "      <td>广州市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>2583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>WEIXIN</td>\n",
       "      <td>2</td>\n",
       "      <td>海南省</td>\n",
       "      <td>海口市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>2181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>10</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>宝鸡市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>山西省</td>\n",
       "      <td>太原市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>2</td>\n",
       "      <td>四川省</td>\n",
       "      <td>南充市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>辽宁省</td>\n",
       "      <td>沈阳市</td>\n",
       "      <td>201909</td>\n",
       "      <td>201910</td>\n",
       "      <td></td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>辽宁省</td>\n",
       "      <td>沈阳市</td>\n",
       "      <td>201909</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201910</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>201909</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>201910</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>201911</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3792</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>广东省</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201907</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3793</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>广东省</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201907</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3794</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>广东省</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201907</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3795</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>荆门市</td>\n",
       "      <td>201907</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3796</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>荆门市</td>\n",
       "      <td>201907</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3797</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>荆门市</td>\n",
       "      <td>201907</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3798</th>\n",
       "      <td>HUABEI</td>\n",
       "      <td>6</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>荆门市</td>\n",
       "      <td>201907</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3799</th>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3800</th>\n",
       "      <td>FENQICHAOREN</td>\n",
       "      <td>2</td>\n",
       "      <td>江苏省</td>\n",
       "      <td>南京市</td>\n",
       "      <td>201909</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3801</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201907</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3802</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201907</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3803</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201907</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3804</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>广东省</td>\n",
       "      <td>东莞市</td>\n",
       "      <td>201907</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3805</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>浙江省</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>201908</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3806</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>浙江省</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>201908</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3807</th>\n",
       "      <td>SHIJIYINTONG</td>\n",
       "      <td>9</td>\n",
       "      <td>浙江省</td>\n",
       "      <td>杭州市</td>\n",
       "      <td>201908</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3808</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>四川省</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201908</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3809</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>四川省</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201908</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3810</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>四川省</td>\n",
       "      <td>成都市</td>\n",
       "      <td>201908</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3811</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201907</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3812</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201907</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3813</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201907</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3814</th>\n",
       "      <td>JINGDONG</td>\n",
       "      <td>7</td>\n",
       "      <td>新疆维吾尔自治区</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>201907</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3815</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3816</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3817</th>\n",
       "      <td>TIANCHENGRONGZU</td>\n",
       "      <td>1</td>\n",
       "      <td>湖北省</td>\n",
       "      <td>黄冈市</td>\n",
       "      <td>201908</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td>206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3818</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>广东省</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201909</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3819</th>\n",
       "      <td>DELEKEJI</td>\n",
       "      <td>9</td>\n",
       "      <td>广东省</td>\n",
       "      <td>中山市</td>\n",
       "      <td>201909</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3820</th>\n",
       "      <td>MASHANGXIAOJIN</td>\n",
       "      <td>2</td>\n",
       "      <td>陕西省</td>\n",
       "      <td>渭南市</td>\n",
       "      <td>201910</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3821</th>\n",
       "      <td>YUEBAO</td>\n",
       "      <td>10</td>\n",
       "      <td>广东省</td>\n",
       "      <td>佛山市</td>\n",
       "      <td>201910</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3822 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  合作方  用户类型        省份     地市    完成时间      账期 逾期人数  总竣工人数\n",
       "0        FENQICHAOREN     2       陕西省    榆林市  201910  201911        6120\n",
       "1            JINGDONG     7  新疆维吾尔自治区  乌鲁木齐市  201910  201911          16\n",
       "2              YUEBAO    10       陕西省    安康市  201910  201911          60\n",
       "3        FENQICHAOREN     2       江苏省    南京市  201910  201911         130\n",
       "4            JINGDONG     7       福建省    漳州市  201910  201911          13\n",
       "5              HUABEI     6       陕西省    咸阳市  201910  201911        1375\n",
       "6     TIANCHENGRONGZU     1       陕西省    榆林市  201910  201911        1260\n",
       "7      YUANDONGRONGZU     2       海南省    海口市  201910  201911        4230\n",
       "8            DELEKEJI     9       福建省    漳州市  201910  201911          62\n",
       "9            DELEKEJI     9       江苏省    南京市  201909  201910          17\n",
       "10           DELEKEJI     9       江苏省    南京市  201909  201911          17\n",
       "11     YUANDONGRONGZU     2       山西省    太原市  201910  201911         344\n",
       "12       FENQICHAOREN     2       四川省    绵阳市  201910  201911        2535\n",
       "13           JINGDONG     7       江苏省    南京市  201910  201911          21\n",
       "14           JINGDONG     7       陕西省    榆林市  201910  201911         141\n",
       "15     MASHANGXIAOJIN     2       四川省    成都市  201910  201911         339\n",
       "16     MASHANGXIAOJIN     2       广东省    广州市  201910  201911        2583\n",
       "17           JINGDONG     7       广东省    东莞市  201910  201911          16\n",
       "18             WEIXIN     2       海南省    海口市  201910  201911        2181\n",
       "19             YUEBAO    10       陕西省    宝鸡市  201910  201911          23\n",
       "20    TIANCHENGRONGZU     1       广东省    东莞市  201910  201911         897\n",
       "21             HUABEI     6       山西省    太原市  201910  201911         945\n",
       "22           DELEKEJI     9       江苏省    南京市  201910  201911          12\n",
       "23     MASHANGXIAOJIN     2       四川省    南充市  201910  201911         447\n",
       "24           JINGDONG     7       辽宁省    沈阳市  201909  201910          39\n",
       "25           JINGDONG     7       辽宁省    沈阳市  201909  201911          39\n",
       "26    TIANCHENGRONGZU     1  新疆维吾尔自治区  乌鲁木齐市  201910  201911         609\n",
       "27       SHIJIYINTONG     9       湖北省    黄冈市  201908  201909           1\n",
       "28       SHIJIYINTONG     9       湖北省    黄冈市  201908  201910           1\n",
       "29       SHIJIYINTONG     9       湖北省    黄冈市  201908  201911           1\n",
       "...               ...   ...       ...    ...     ...     ...  ...    ...\n",
       "3792           HUABEI     6       广东省    中山市  201907       2         278\n",
       "3793           HUABEI     6       广东省    中山市  201907       3         278\n",
       "3794           HUABEI     6       广东省    中山市  201907       4         278\n",
       "3795           HUABEI     6       湖北省    荆门市  201907       1         124\n",
       "3796           HUABEI     6       湖北省    荆门市  201907       2         124\n",
       "3797           HUABEI     6       湖北省    荆门市  201907       3         124\n",
       "3798           HUABEI     6       湖北省    荆门市  201907       4         124\n",
       "3799     FENQICHAOREN     2       江苏省    南京市  201909       1          17\n",
       "3800     FENQICHAOREN     2       江苏省    南京市  201909       2          17\n",
       "3801         JINGDONG     7       广东省    东莞市  201907       1           6\n",
       "3802         JINGDONG     7       广东省    东莞市  201907       2           6\n",
       "3803         JINGDONG     7       广东省    东莞市  201907       3           6\n",
       "3804         JINGDONG     7       广东省    东莞市  201907       4           6\n",
       "3805     SHIJIYINTONG     9       浙江省    杭州市  201908       1         105\n",
       "3806     SHIJIYINTONG     9       浙江省    杭州市  201908       2         105\n",
       "3807     SHIJIYINTONG     9       浙江省    杭州市  201908       3         105\n",
       "3808  TIANCHENGRONGZU     1       四川省    成都市  201908       1           2\n",
       "3809  TIANCHENGRONGZU     1       四川省    成都市  201908       2           2\n",
       "3810  TIANCHENGRONGZU     1       四川省    成都市  201908       3           2\n",
       "3811         JINGDONG     7  新疆维吾尔自治区  乌鲁木齐市  201907       1          10\n",
       "3812         JINGDONG     7  新疆维吾尔自治区  乌鲁木齐市  201907       2          10\n",
       "3813         JINGDONG     7  新疆维吾尔自治区  乌鲁木齐市  201907       3          10\n",
       "3814         JINGDONG     7  新疆维吾尔自治区  乌鲁木齐市  201907       4          10\n",
       "3815  TIANCHENGRONGZU     1       湖北省    黄冈市  201908       1         206\n",
       "3816  TIANCHENGRONGZU     1       湖北省    黄冈市  201908       2         206\n",
       "3817  TIANCHENGRONGZU     1       湖北省    黄冈市  201908       3         206\n",
       "3818         DELEKEJI     9       广东省    中山市  201909       1          26\n",
       "3819         DELEKEJI     9       广东省    中山市  201909       2          26\n",
       "3820   MASHANGXIAOJIN     2       陕西省    渭南市  201910       1         968\n",
       "3821           YUEBAO    10       广东省    佛山市  201910       1          39\n",
       "\n",
       "[3822 rows x 8 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# last = pd.DataFrame(columns=('合作方','用户类型','省份','完成时间','账期','MOB','放款金额'))\n",
    "users     = []\n",
    "types     = []\n",
    "provinces = []\n",
    "city = []\n",
    "# operator  = []\n",
    "time      = []\n",
    "billing   = []\n",
    "mob       = []\n",
    "yuqi_num  = []\n",
    "num       = []\n",
    "# money     = []\n",
    "# 遍历df每一行\n",
    "for i,r in df.iterrows():\n",
    "    # 最高账期月\n",
    "    y = 201911 - r['完成时间']\n",
    "    if y >= 189:\n",
    "        z = y - 176\n",
    "    elif y >= 89:\n",
    "        z = y - 88\n",
    "    else:\n",
    "        z = y\n",
    "    j = 1\n",
    "    while j <= z:\n",
    "        \n",
    "        month = int(str(r['完成时间'])[4:])\n",
    "        if month + j <=12:\n",
    "            b_month = r['完成时间'] + j\n",
    "        elif month + j > 12 and month + j <=24:\n",
    "            b_month = r['完成时间'] + j + 88\n",
    "        elif month + j > 24:\n",
    "            b_month = r['完成时间'] + j + 176\n",
    "\n",
    "        \n",
    "        users.append(r['合作方'])\n",
    "        types.append(r['用户类型'])\n",
    "        provinces.append(r['省份'])\n",
    "#         operator.append(r['运营商'])\n",
    "        city.append(r['地市'])\n",
    "        time.append(r['完成时间'])\n",
    "        billing.append(b_month)\n",
    "        mob.append(j)\n",
    "        yuqi_num.append('')\n",
    "        num.append(r['总竣工人数'])\n",
    "\n",
    "#         last = last.append(x,ignore_index=True,sort=False)\n",
    "#         last = pd.concat([last,x],ignore_index=True,sort=False)\n",
    "        j += 1\n",
    "# 合成账期数据\n",
    "data = {\n",
    "    '合作方': users,\n",
    "    '用户类型': types,\n",
    "    '省份': provinces,\n",
    "    '地市': city,\n",
    "    '完成时间': time,\n",
    "    '账期': billing,\n",
    "    '逾期人数': yuqi_num,\n",
    "#     'MOB': mob,\n",
    "    '总竣工人数': num\n",
    "}\n",
    "last = pd.DataFrame(data)\n",
    "# 合成mob数据\n",
    "data1 = {\n",
    "    '合作方': users,\n",
    "    '用户类型': types,\n",
    "    '省份': provinces,\n",
    "    '地市': city,\n",
    "    '完成时间': time,\n",
    "    '账期': mob,\n",
    "    '逾期人数': yuqi_num,\n",
    "    '总竣工人数': num\n",
    "}\n",
    "last1 = pd.DataFrame(data1)\n",
    "# 合并账期和mob数据\n",
    "last2 = pd.concat([last,last1],ignore_index=True,sort=False)\n",
    "\n",
    "last2\n",
    "\n",
    "# last2.to_excel('vintage_data_output.xlsx',index=False)\n",
    "# print('success!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-11T06:53:02.001716Z",
     "start_time": "2019-11-11T06:53:01.982790Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['记录数', 'area', 'c_time', 'city', 'hzf', 'leix', 'num', 'season']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.read_csv('vintage.csv',low_memory=False)\n",
    "list(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-11-11T06:53:05.716065Z",
     "start_time": "2019-11-11T06:53:04.670865Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "success!\n"
     ]
    }
   ],
   "source": [
    "# 计算mob函数\n",
    "def mob(x):\n",
    "    season = x['season']\n",
    "    c_time = x['c_time']\n",
    "    y = season - c_time\n",
    "    if y >= 189:\n",
    "        z = y - 176\n",
    "    elif y >= 89:\n",
    "        z = y - 88\n",
    "    else:\n",
    "        z = y\n",
    "    return z\n",
    "\n",
    "df1['mob']=df1.apply(lambda x:mob(x),axis=1)\n",
    "df1['总竣工人数']= ''\n",
    "df2 = df1.rename(columns={'记录数':'记录数','hzf':'合作方', 'leix':'用户类型','area':'省份','city':'地市','c_time':'完成时间','season':'账期','mob':'mob','num':'逾期人数'},inplace=False)\n",
    "\n",
    "last3 = df2.drop(columns=['记录数','mob'])\n",
    "last4 = df2.drop(columns=['记录数','账期'])\n",
    "last5 = last4.rename(columns={'mob':'账期'})\n",
    "# 合并3个df\n",
    "last6 = pd.concat([last2,last3,last5],ignore_index=True,sort=False)\n",
    "last6.to_excel('vintage_data_output.xlsx',index=False)\n",
    "print('success!')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
